package com.hz.framework.dao;

import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.ArrayList;
import java.util.Enumeration;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.StringTokenizer;

import javax.servlet.http.HttpServletRequest;

import com.hz.framework.util.CharacterDispose;


public class ContextCMP extends DBQueryServ {
	/***
	 * 直接用连接池连接数据库的公共方法
	 * */

	public ContextCMP() {
	}

	public int getICount() {
		return iCount;
	}

	public void setICount(int count) {
		iCount = count;
	}
	
	/**
	 * 存储页面name控件名
	 *  
	 * @param HttpServletRequest request 获取页面控件值
	 * @param String[] hidName           页面隐藏域控件name名
	 * 
	 * @return List lstName              存储name值
	 */
	public List doEName(HttpServletRequest request,String hidName[]){
		
		/* 存储 Enumeration eName值*/
		List lstName = new ArrayList();
		
		/* 获取页面name控件名 */
		Enumeration eName  = request.getParameterNames();

		while(eName.hasMoreElements()){
			String strValue = eName.nextElement().toString().trim();
			boolean flag = true;
			if("ins".equals(strValue)){
				continue;
			}else if("sel".equals(strValue)){
				continue;
			}else if("upd".equals(strValue)){
				continue;
			}else if("del".equals(strValue)){
				continue;
			}else if("pause".equals(strValue)){
				continue;
			}else if("resume".equals(strValue)){
				continue;
			}
			if(hidName != null){
				for(int i = 0;i < hidName.length;i++){
					if(hidName[i].equals(strValue)){
						flag = false;
						break;
					}
				}
			}
			if(flag){
				lstName.add(strValue);
			}
		}

		return lstName;
	}

	/**
	 * 生成插入语句并赋值
	 *  
	 * @param HttpServletRequest request 获取页面控件值
	 * @param Strig table                数据库表名
	 * @param String[] column            表的字段名
	 * @param String[] hidName           页面隐藏域控件name名
	 * 
	 * @return
	 */
	public void doInsert(HttpServletRequest request, String table,
			String[] column,String[] hidName) throws Exception {

		/* 组成SQL */
		sbSQL = new StringBuilder();
		
		/* 组成表列 */
		sbColumn = new StringBuilder();
		
		/* 组成VALUES ? */
		sbValue = new StringBuilder();
		
		/* 存储 Object 数组的数据 */
		lstValue = new ArrayList();
		
		/* 调用 */
		List lstName = null;
			
		/**
		 * 判断自动还是手动获取控件名
		 * */
		
		if(column == null){
			lstName = doEName(request,hidName);
			iLength = lstName.size();
			strVName = lstName.get(0).toString();
		}else{
			iLength = column.length;
			strVName  = column[0];
		}
		
		/* 分隔表名和首列*/
		String[] strTable = table.split("/");
		
		if(strTable.length > 1){
			strVName = strTable[1];
		}
		/**
		 * 生成 INSERT SQL及获取控件对象值
		 * */
		String[] strColumnV =  request.getParameterValues(strVName);
		if(strColumnV != null){
			
			sbColumn.append("INSERT INTO ");
			sbColumn.append(strTable[0]);
			sbColumn.append(" (");
			sbValue.append(" VALUES(");
			boolean flag = false;			
			for(int j=0;j<strColumnV.length;j++){
				/* 获取控件值*/
				boolean flagM = true;	
				strValueRequest = new String[iLength];

				if("".equals(strColumnV[j]) || strColumnV[j] ==null){					
					if(flag){
						continue;
					}else{
						flagM = false;
					}
				}

				for (int i = 0;i<iLength; i++) {					
					String strV = "";
					if(column == null){
						strV = lstName.get(i).toString();
					}else{
						strV = column[i];
					}
					if( j == 0 ){
						sbColumn.append(strV);
						sbValue.append("?");
						if(i != iLength -1){
							sbColumn.append(",");
							sbValue.append(",");							
						}
						flag = true;
					}
					if(flagM){
						if(request.getParameterValues(strV).length > 1){
							strValueRequest[i] = request.getParameterValues(strV)[j];
						}else{
							strValueRequest[i] = request.getParameterValues(strV)[0];
						}
					}
				}
				if(flagM){
					lstValue.add(strValueRequest);
				}
			}

			sbValue.append(")");
			sbColumn.append(")");
			sbSQL.append(sbColumn);
			sbSQL.append(sbValue);
			/* 执行INSERTSQL */
			for(int i = 0;i<lstValue.size();i++){
				qr.update(sbSQL.toString(),(String[])lstValue.get(i));	
			}
		}		
	}

	/**
	 * 生成修改语句及给WHERE条件赋值
	 * 
	 * @param HttpServletRequest request 获取页面控件值
	 * @param Strig table                数据库表名
	 * @param String[] column            表的字段名
	 * @param String[] whereValue        SQL语句WHERE条件
	 * @param String[] hidName           页面隐藏域控件name名
	 * 
	 * @return
	 */
	public void doUpdate(HttpServletRequest request, String table,
			String[] column,String whereValue,String[] hidName) throws Exception {
	
		/* 组成表列 */
		sbColumn = new StringBuilder();
			
		/* 存储 Object 数组的数据 */
		lstValue = new ArrayList();
		
				
		/* 获取分隔后的字段数组 */
		lstSeparatorColumns = doSeparator(whereValue);
		
		/**
		 *  获取strSeparatorColumns 数组长度
		 **/
		
		if(!lstSeparatorColumns.isEmpty()){
			iLength = lstSeparatorColumns.size();
		}
		/* 调用 */
		List lstName = null;
		
		/**
		 * 判断自动还是手动获取控件名
		 * */		
		if(column == null){
			lstName = doEName(request,hidName);
			iVerdict = lstName.size();
			strVName = lstName.get(0).toString();
		}else{
			iVerdict = column.length;
			strVName  = column[0];
		}
		
		/* 分隔表名和首列*/
		String[] strTable = table.split("/");		
		if(strTable.length > 1){
			strVName = strTable[1];
		}	
		
		/* 设置Object[]数据的长度*/
		int iObj = iVerdict + iLength;		
		 
		/**
		 * 生成 UPDATE SQL及获取控件对象值
		 **/	
		
		sbColumn.append("UPDATE ");
		sbColumn.append(strTable[0]);
		sbColumn.append(" SET ");
		String[] strColumnV =  request.getParameterValues(strVName);
		if(strColumnV != null){
			for(int j=0;j<strColumnV.length;j++){
				/* 获取控件值*/
				strValueRequest = new String[iObj];
				int iJ= 0;
				for (int i = 0; i < iObj; i++) {
	
					if(i <= iVerdict-1){
						if(column == null){
							strValue = lstName.get(i).toString();
						}else{
							strValue = column[i];
						}						
					}else{					
						strValue = lstSeparatorColumns.get(iJ).toString();
						iJ++;
					}	
				
					if(request.getParameterValues(strValue).length > 1){
						strValueRequest[i] = request.getParameterValues(strValue)[j];
					}else{
						strValueRequest[i] = request.getParameterValues(strValue)[0];
					}
					if(strValueRequest[i] == null || "null".equals(strValueRequest[i])){
						strValueRequest[i] = "";
					}
					if(j == 0){
						if(i <= iVerdict-1){
							sbColumn.append(strValue);				
							sbColumn.append("=?");
							if(i != iVerdict -1 ){
								sbColumn.append(",");
							}		
						}
					}
				}			
				lstValue.add(strValueRequest);
			}
			sbColumn.append(" ");
			sbColumn.append(whereValue.toLowerCase());
			
			/* 执行UPDATESQL */		
			for(int i = 0;i<lstValue.size();i++){		
				/*for(int j=0;j<((String[])lstValue.get(i)).length;j++){
					System.out.println(((String[])lstValue.get(i))[j]);
				}*/
				qr.update(sbColumn.toString(),(String[])lstValue.get(i));			
			}
		}
			
	}

	/**
	 * 生成删除语句及给WHERE条件赋值
	 * 
	 * @param HttpServletRequest request 获取页面控件值
	 * @param Strig sql                  删除SQL语句
	 * @param String[] column            WHERE条件的列名
	 * 
	 * @return 
	 */
	public void doDelete(HttpServletRequest request,
			String sql, String[] column) throws Exception {
		
		/* 存储 Object 数组的数据 */
		lstValue = new ArrayList();	
				
		/**
		 * 判断自动还是手动获取控件名
		 * */		
		if(column == null){
			
			/* 获取分隔后的字段数组 */
			lstSeparatorColumns = doSeparator(sql.substring(sql.indexOf("where")));
			
			/**
			 *  获取strSeparatorColumns 数组长度
			 **/
			
			if(!lstSeparatorColumns.isEmpty()){
				iLength = lstSeparatorColumns.size();				
			}	
			
			iVerdict = iLength;
			
			strVName = lstSeparatorColumns.get(0).toString();
			
		}else{
			iVerdict = column.length;
			strVName = column[0];
		}
		
		/**
		 * 获取 DELETE SQL控件对象值
		 * */
		String[] strColumnV =  request.getParameterValues(strVName);
		if(strColumnV != null){
			for(int j=0;j<strColumnV.length;j++){
				/* 获取控件值*/
				strValueRequest = new String[iVerdict];
				
				for (int i = 0; i < iVerdict; i++) {
	
					if(column == null){
						strValue = lstSeparatorColumns.get(i).toString();
					}else{
						strValue = column[i];
					}
					strValueRequest[i] = request.getParameterValues(strValue)[j];				
				}			
				lstValue.add(strValueRequest);
			}
		
			/* 执行INSERTSQL */
			for(int i = 0;i<lstValue.size();i++){
				qr.update(sql,(String[])lstValue.get(i));	
			}
		}
	}

	/**
	 * 生成查询语句
	 * 
	 * @param Strig table                数据库表名
	 * @param String[] column            查询的字段名
	 * @param String sqlWhere            手动生成SQL的where条件
	 * 
	 * @return String sbSQL 返回生成SQL语句
	 */
	public String doSelectColumn(String table,
			String[] column,String sqlWhere) throws Exception {
			
		/* 组成表列 */
		sbColumn = new StringBuilder();
		sbColumn.append("SELECT ");		
		
		/**
		 * 生成INSERTSQL 及 获取 控件对象值
		 * */
		
		for (int i = 0; i < column.length; i++) {			
			sbColumn.append(column[i]);						
			if(i != column.length-1){
				sbColumn.append(",");
			}				
		}
		sbColumn.append(" FORM ");
		sbColumn.append(table);			
		sbColumn.append(sqlWhere);			
		
		return sbColumn.toString();
	}
	
	/**
	 * 给SQL语句WHERE条件赋值
	 * 
	 * @param HttpServletRequest request 获取页面控件值	
	 * @param Strig whereSQL             SQL语句where条件
	 * @param String[] column            获取SQL语句的where条件的页面控件值
	 * @param boolean flagV              判断控件名带是否有cvr,如flagV值为true 将列名替换成cvr列名
	 * @return String sbSQL 返回生成SQL语句
	 */
	public String[] doWhereValue(HttpServletRequest request,String whereSQL,String[] column,boolean flagV) throws Exception {
		
		/**
		 * 判断自动还是手动获取控件名
		 * */		
		if(column == null){
			
			/* 获取分隔后的字段数组 */
			lstSeparatorColumns = doSeparator(whereSQL);
			
			/**
			 *  获取strSeparatorColumns 数组长度
			 **/
			
			if(!lstSeparatorColumns.isEmpty()){
				iLength = lstSeparatorColumns.size();				
			}				
			iVerdict = iLength;		
		}else{
			iVerdict = column.length;
		}
		/* 获取控件值*/
		strValueRequest = new String[iVerdict];
		
		for (int i = 0; i < iVerdict; i++) {	
			if(column == null){
				strValue = lstSeparatorColumns.get(i).toString();
			}else{
				strValue = column[i];
			}			
			if(flagV){
				strValue = strValue.replace(strValue, "cvr"+strValue);
			}
			strValueRequest[i] = request.getParameter(strValue);
			if(strValueRequest[i] == null || "null".equals(strValueRequest[i])){
				strValueRequest[i] ="";
			}
		}
				
		return strValueRequest;
	}	
	
	public List getLstName() {
		return lstName;
	}

	public void setLstName(List lstName) {
		this.lstName = lstName;
	}

	/**
	 * 分隔获取WHERE条件里的字段 
	 * 
	 * @param String whereValue   获取SQL语句的where条件	             
	 * 
	 * @return List strSeparator  存储分隔后的字段
	 */
	 public List doSeparator(String whereValue){
		if(lstName != null){
			lstName.clear();
			lstName = null;
		}
		List lstSeparator = new ArrayList();
		if(whereValue.indexOf("?") != -1){
			StringTokenizer st = new StringTokenizer(whereValue,SEPARATOR);
			while(st.hasMoreTokens()){
				String strV = CharacterDispose.doReplace(st.nextToken());
				if(!"".equals(strV)){
					lstSeparator.add(strV);
				}
			}
		}
		setLstName(lstSeparator);
		return lstSeparator;
	}
	
	private List lstName = null;
	 
	private int iCount = 0;
	
	/* 获取控件值*/
	private String strValueRequest [] = null;
	
	/* 组成SQL */
	private StringBuilder sbSQL = null;
	
	/* 组成表列 */
	private StringBuilder sbColumn = null;
	
	/* 组成VALUES ? */
	private StringBuilder sbValue = null;

	/* 存储 Object 数组的数据 */
	private List lstValue = null;
	
	/* 存储分隔后的WHERE条件里的字段 */
	private List lstSeparatorColumns = null;
	
	/* 设置自动还是手动获取控件名数据的长度*/
	private int iVerdict = 0;
	
	/* 页面控件name名 */
	private String strVName = "";
	
	/* 设置 strSeparatorColumns 数组长度 */
	private int iLength = 0;
	
	/* 获取控件name名 */
	private String strValue = "";
	
	/* 分隔符 */
	private static final String SEPARATOR = "1=?' ()%";    
	//--------------------------------------------------------------------
    public List jsonList(String sql)throws Exception{
        List list = new ArrayList();
        ResultSet rs = executeQuery(sql);
        ResultSetMetaData rsmd = rs.getMetaData();
        while(rs.next()){
            Map map = new HashMap();
          for(int i=1;i<=  rsmd.getColumnCount();i++){
              String value = (String) rs.getString(i);
              if("null".equalsIgnoreCase(value))value="";
              map.put(rsmd.getColumnLabel(i),value);
          }
          list.add(map);
        }
        return list;
    }  
    
    public Map jsonObject(String sql)throws Exception{
        List list = new ArrayList();
        ResultSet rs = executeQuery(sql);
        ResultSetMetaData rsmd = rs.getMetaData();
        Map object = new HashMap();
        if(rs.next()){
          for(int i=1;i<=  rsmd.getColumnCount();i++){
              String value = (String) rs.getString(i);
              if("null".equalsIgnoreCase(value))value="";
              object.put(rsmd.getColumnLabel(i),value);
          }
          list.add(object);
        }
        return object;
    }
    
    public Map jsonListmap(String sql)throws Exception{
    	Map maps=new HashMap();
    	String stmt="";
        List list = new ArrayList();
        ResultSet rs = executeQuery(sql);
        ResultSetMetaData rsmd = rs.getMetaData();
        while(rs.next()){
            Map map = new HashMap();
          for(int i=1;i<=  rsmd.getColumnCount();i++){
              String value = (String) rs.getString(i);
              if("null".equalsIgnoreCase(value))value="";
              map.put(rsmd.getColumnLabel(i),value);
              stmt+=rsmd.getColumnLabel(i)+",";
          }
          list.add(map);
        }
        maps.put("list", list);
        maps.put("stmt", stmt);
        return maps;
    }
	
}
